<?php
declare (strict_types = 1);
namespace app\admin\controller;

use think\Request;
use think\Exception;
use think\exception\ValidateException;

use \PHPExcel;
use \PHPExcel_Style_Color;
use \PHPExcel_Style_Alignment;
use \PHPExcel_style_Fill;
use \PHPExcel_Style_Border;

use app\admin\controller\BaseController;
use service\JsonService as Json;
use service\ChoiceService;

use app\common\model\User;
use app\common\model\CheckRule;
use app\common\model\CheckClock as Clock;
use app\common\model\ApplyMissCard as Miss;
use app\common\model\ApplyExpenses;
use app\common\model\ApplyWorkOver;
use app\common\model\ApplyLeave;
use app\common\model\ApplyLeaveType;
use app\common\model\UserSection;
use app\common\model\CheckClock;
use app\common\model\SystemMenu;
use app\common\model\Calendar;

use app\common\validate\CheckRuleValidate;
use app\common\validate\WorkOverValidate;
use app\common\validate\ExpensesValidate;
use app\common\validate\LeaveValidate;
use think\console\output\question\Choice;

class CheckController extends BaseController
{
    /**
     * 考勤规则列表
     */
    public function getCheckRuleList()
    {
        $list = CheckRule::getList([], 0);
        if(!$list) return Json::fail('暂无数据');

        foreach($list as &$info){
            if(empty($info['section_ids'])){
                $info['section'] = '';
            }else{
                $sectionIds = explode(',', $info['section_ids']);
                $sectionName = UserSection::getCol([['id', 'in', $sectionIds]], 'name');
                $info['section'] = implode('、', $sectionName);
            }

            $info['start_time'] = secondToHour($info['start_time']);
            $info['end_time']   = secondToHour($info['end_time']);
        }

        return Json::success('ok', $list);
    }

    /**
     * 考勤规则新增
     */
    public function addCheckRule()
    {
        $param = input('');

        try{
            validate(CheckRuleValidate::class)->scene('add')->check($param);
        }catch(ValidateException $e){
            return Json::fail($e->getError());
        }

        $param['start_time'] = hourToSecond($param['start_time']);
        $param['end_time']   = hourToSecond($param['end_time']);

        $flag = CheckRule::add($param);
        if(!$flag) return Json::fail('添加失败');
        return Json::success('添加成功');
    }

    /**
     * 考勤规则编辑
     */
    public function setCheckRule()
    {
        $param = input('');

        try{
            validate(CheckRuleValidate::class)->scene('edit')->check($param);
        }catch(ValidateException $e){
            return Json::fail($e->getError());
        }

        $param['start_time'] = hourToSecond($param['start_time']);
        $param['end_time']   = hourToSecond($param['end_time']);

        $flag = CheckRule::edit($param);
        if(!$flag) return Json::fail('编辑失败');
        return Json::success('编辑成功');
    }

    /**
     * 考勤规则删除
     */
    public function delCheckRule()
    {
        $checkRuleId = Request()->param('check_rule_id', 0);
        if($checkRuleId == 0) return Json::fail('数据错误');

        $isDel = User::getFind(['check_clock_id' => $checkRuleId]);
        if($isDel) return Json::fail('该规则还在使用中，不能删除');

        $flag = CheckRule::destroy($checkRuleId);
        if(!$flag) return Json::fail('删除失败');
        return Json::success('删除成功');
    }

    /**
     * 考勤记录
     */
    public function getCheckRecordList()
    {
        $page = Request()->param('page', 1);

        $sectionId  = Request()->param('section_id', 0);
        $userSearch = Request()->param('user_search', '');
        $startTime  = Request()->param('start_time', '');
        $endTime    = Request()->param('end_time', '');
        $type       = Request()->param('type', 0);

        $where = [];

        if($sectionId != 0 && empty($userSearch)){
            $userIds = User::getCol(['section_id' => $sectionId], 'id');
            $where[] = ['user_id', 'in', $userIds];
        }else if($sectionId == 0 && !empty($userSearch)){
            $userIds = User::getCol([['name|staff_no', 'like', '%' . $userSearch . '%']], 'id');
            $where[] = ['user_id', 'in', $userIds];
        }else if($sectionId != 0 && !empty($userSearch)){
            $sectionIds = User::getCol(['section_id' => $sectionId], 'id');
            $staffIds   = User::getCol([['name|staff_no', 'like', '%' . $userSearch . '%']], 'id');

            $userIds = array_intersect($sectionIds, $staffIds);

            if(!$userIds) return Json::fail('暂无数据');
            $where[] = ['user_id', 'in', $userIds];
        }

        if(!empty($startTime) && empty($endTime)){
            $where[] = ['create_time', '>=', strtotime($startTime)];
        }else if(empty($startTime) && !empty($endTime)){
            $where[] = ['create_time', '<=', strtotime($endTime) + 86399];
        }else if(!empty($startTime) && !empty($endTime)){
            $where[] = ['create_time', 'between', [strtotime($startTime), strtotime($endTime) + 86399]];
        }

        if($type != 0
            && $sectionId == 0 
            && empty($userSearch)
            && empty($startTime)
            && empty($endTime)
        ){
            $timeField = $type == 5 ? 'start_time' : 'create_time';
            $where[] = [$timeField, 'between', [strtotime(date('Y-m-d')), strtotime(date('Y-m-d')) + 86399]];
        }

        switch ($type) {
            case 2:
                $where[] = ['clock_status', '=', 2];
                break;
            case 3:
                $where[] = ['clock_status', '=', 3];
                break;
            case 4:
                $where[] = ['is_out', '=', 1];
                break;
            case 5:
                $list = ApplyLeave::getList($where, 0);
                if(!$list) return Json::fail('暂无数据');

                foreach($list as &$info){
                    $userInfo = User::getFind(['id' => $info['user_id']], 'name');
        
                    $info['leave_type']   = ApplyLeaveType::getVal(['id' => $info['leave_type_id']], 'name');
                    $info['start_time']   = date('Y-m-d H:i', $info['start_time']);
                    $info['end_time']     = date('Y-m-d H:i', $info['end_time']);
                    $info['user_name']    = $userInfo['name'];
        
                    unset($info['user_id']);
                    unset($info['leave_type_id']);
                }

                return Json::success('ok', $list);
                break;
        }

        $list = CheckClock::getList($where, 1, $page, 20, '*', 'create_time DESC');
        if(!$list) return Json::fail('暂无数据');

        $clockType   = [1 => '上班', 2 => '下班'];
        $clockstatus = [1 => '正常', 2 => '迟到', 3 => '早退'];

        foreach($list as &$info){
            $info['user_name']    = User::getVal(['id' => $info['user_id']], 'name');
            $info['clock_type']   = $clockType[$info['clock_type']];
            $info['clock_status'] = $clockstatus[$info['clock_status']];
            $info['work_time']    = secondToHours($info['work_time']);
            $info['status_time']  = secondToHours($info['status_time']);
        }

        return Json::success('ok', $list, CheckClock::getNum($where));
    }

    /**
     * 考勤数据
     */
    public function getCheckData()
    {
        $sectionId   = Request()->param('section_id', 0);
        $userSearch  = Request()->param('user_search', '');
        $startTime   = Request()->param('start_time', '');
        $endTime     = Request()->param('end_time', '');

        $where = [];

        if($sectionId != 0 && empty($userSearch)){
            $userIds = User::getCol(['section_id' => $sectionId], 'id');
            $where[] = ['user_id', 'in', $userIds];
        }else if($sectionId == 0 && !empty($userSearch)){
            $userIds = User::getCol([['name|staff_no', 'like', '%' . $userSearch . '%']], 'id');
            $where[] = ['user_id', 'in', $userIds];
        }else if($sectionId != 0 && !empty($userSearch)){
            $sectionIds = User::getCol(['section_id' => $sectionId], 'id');
            $staffIds   = User::getCol([['name|staff_no', 'like', '%' . $userSearch . '%']], 'id');

            $userIds = array_intersect($sectionIds, $staffIds);

            if(!$userIds) return Json::fail('暂无数据');
            $where[] = ['user_id', 'in', $userIds];
        }

        if(!empty($startTime) && empty($endTime)){
            $where[] = ['create_time', '>=', strtotime($startTime)];
        }else if(empty($startTime) && !empty($endTime)){
            $where[] = ['create_time', '<=', strtotime($endTime) + 86399];
        }else if(!empty($startTime) && !empty($endTime)){
            $where[] = ['create_time', 'between', [strtotime($startTime), strtotime($endTime) + 86399]];
        }

        if($sectionId == 0 
            && empty($userSearch)
            && empty($startTime)
            && empty($endTime)
        ){
            $whereTime[]      = ['create_time', 'between', [strtotime(date('Y-m-d')), strtotime(date('Y-m-d')) + 86399]];
            $whereLeaveTime[] = ['start_time', 'between', [strtotime(date('Y-m-d')), strtotime(date('Y-m-d')) + 86399]];
        }

        /** 打卡次数 */
        $checkNum = CheckClock::where($where)->where($whereTime)->count();

        /** 迟到次数 */
        $lateNum = CheckClock::where($where)->where($whereTime)->where(['clock_status' => 2])->count();

        /** 早退次数 */
        $earlyeNum = CheckClock::where($where)->where($whereTime)->where(['clock_status' => 3])->count();

        /** 外勤次数 */
        $outNum = CheckClock::where($where)->where($whereTime)->where(['is_out' => 1])->count();

        /** 请假人数 */
        $leaveNum = ApplyLeave::where($where)->where($whereLeaveTime)->count();

        $backData = [
            'check_num' => $checkNum,
            'late_num'  => $lateNum,
            'early_num' => $earlyeNum,
            'out_num'   => $outNum,
            'leave_num' => $leaveNum,
        ];

        return Json::success('ok', $backData);
    }

    


    /**
     * 补卡申请列表
     */
    public function getMissCardList()
    {
        $page = Request()->param('page', 1);

        $where = '';

        if($this->user_id != 1){
            $sectionId = User::getVal(['id' => $this->user_id], 'section_id');
            $userIds   = User::getCol([['section_id', '=', $sectionId], ['status', '=', 1]], 'id');

            $where = '(approve_id=' . $this->user_id . ' and status in (1,2)) or (send_id=' . $this->user_id . ' and status=2)';
            $where .= ' or user_id in (' . implode(',', $userIds) . ')';
        }

        $field = 'id, user_id, times, remarks, img, status, create_time';
        $list = Miss::getList($where, 1, $page, 20, $field, 'create_time DESC');
        if($list === false) return Json::fail('暂无数据~');

        foreach($list as &$info){
            $userInfo = User::getFind(['id' => $info['user_id']], 'name');

            $info['times']        = date('Y-m-d H:i', $info['times']);
            $info['user_name']    = $userInfo['name'];

            unset($info['user_id']);
        }

        return Json::success('ok', $list, Miss::getNum($where));
    }

    /**
     * 补卡申请通过
     */
    public function setMissCardPass()
    {
        $missCardId = Request()->param('miss_card_id', 0);
        if($missCardId === 0) return Json::fail('参数错误');

        $info = Miss::getFind(['id' => $missCardId]);
        if($info === false) return Json::fail('暂无数据~');
        if($info['status'] != 1) return Json::fail('状态异常');

        $flag = Miss::edit(['id' => $info['id'], 'status' => 2, 'approve_id' => $this->user_id, 'finish_time' => time()]);

        if($flag === false) return Json::fail('通过失败');

        SystemMenu::delRed($info['user_id'], 23, 2, 2, $missCardId);
        return Json::success('通过成功');
    }

    /**
     * 补卡申请驳回
     */
    public function setMissCardBack()
    {
        $missCardId = Request()->param('miss_card_id', 0);
        $backMsg    = Request()->param('back_msg', 0);
        if($missCardId === 0 || empty($backMsg)) return Json::fail('参数错误');

        $info = Miss::getFind(['id' => $missCardId]);
        if($info === false) return Json::fail('暂无数据~');
        if($info['status'] != 1) return Json::fail('状态异常');

        $flag = Miss::edit(['id' => $info['id'], 'status' => 3, 'back_msg' => $backMsg, 'finish_time' => time()]);

        if($flag === false) return Json::fail('驳回失败');

        SystemMenu::delRed($info['user_id'], 23, 2, 2, $missCardId);
        return Json::success('驳回成功');
    }




    /**
     * 报销申请列表
     */
    public function getExpensesList()
    {
        $page = Request()->param('page', 1);

        $where = '';

        if($this->user_id != 1){
            $sectionId = User::getVal(['id' => $this->user_id], 'section_id');
            $userIds   = User::getCol([['section_id', '=', $sectionId], ['status', '=', 1]], 'id');

            $where = '(approve_id=' . $this->user_id . ' and status in (1,2)) or (send_id=' . $this->user_id . ' and status=2)';
            $where .= ' or user_id in (' . implode(',', $userIds) . ')';
        }

        $field = 'id, user_id, amount, times, desc, type, img, status, create_time';
        $list = ApplyExpenses::getList($where, 1, $page, 20, $field, 'create_time DESC');
        if($list === false) return Json::fail('暂无数据~');

        $type = ['其他', '公司花销'];

        foreach($list as &$info){
            $userInfo = User::getFind(['id' => $info['user_id']], 'name');

            $info['times']        = date('Y-m-d H:i', $info['times']);
            $info['type']         = $type[$info['type']];
            $info['user_name']    = $userInfo['name'];
            

            unset($info['user_id']);
        }

        return Json::success('ok', $list, ApplyExpenses::getNum($where));
    }

    /**
     * 报销申请通过
     */
    public function setExpensesPass()
    {
        $expenseId = Request()->param('expense_id', 0);
        if($expenseId === 0) return Json::fail('参数错误');

        $info = ApplyExpenses::getFind(['id' => $expenseId]);
        if($info === false) return Json::fail('暂无数据~');
        if($info['status'] != 1) return Json::fail('状态异常');

        $flag = ApplyExpenses::edit(['id' => $info['id'], 'status' => 2, 'approve_id' => $this->user_id, 'finish_time' => time()]);

        if($flag === false) return Json::fail('通过失败');

        SystemMenu::delRed($info['user_id'], 22, 2, 2, $expenseId);
        return Json::success('通过成功');
    }

    /**
     * 报销申请驳回
     */
    public function setExpensesBack()
    {
        $expenseId = Request()->param('expense_id', 0);
        $backMsg    = Request()->param('back_msg', 0);
        if($expenseId === 0 || empty($backMsg)) return Json::fail('参数错误');

        $info = ApplyExpenses::getFind(['id' => $expenseId]);
        if($info === false) return Json::fail('暂无数据~');
        if($info['status'] != 1) return Json::fail('状态异常');

        $flag = ApplyExpenses::edit(['id' => $info['id'], 'status' => 3, 'back_msg' => $backMsg, 'finish_time' => time()]);

        if($flag === false) return Json::fail('驳回失败');

        SystemMenu::delRed($info['user_id'], 22, 2, 2, $expenseId);
        return Json::success('驳回成功');
    }

    /**
     * 报销申请删除
     */
    public function delExpensesBack()
    {
        $expenseId = Request()->param('expense_id', 0);
        if(!$expenseId) return Json::fail('参数错误');
        $flag = ApplyExpenses::destroy($expenseId);
        if($flag === false) return Json::fail();
        return Json::success();
    }

    


    /**
     * 加班申请列表
     */
    public function getWorkOverList()
    {
        $page = Request()->param('page', 1);

        $where = '';

        if($this->user_id != 1){
            $sectionId = User::getVal(['id' => $this->user_id], 'section_id');
            $userIds   = User::getCol([['section_id', '=', $sectionId], ['status', '=', 1]], 'id');

            $where = '(approve_id=' . $this->user_id . ' and status in (1,2)) or (send_id=' . $this->user_id . ' and status=2)';
            $where .= ' or user_id in (' . implode(',', $userIds) . ')';
        }

        $field = 'id, user_id, start_time, end_time, status, create_time';
        $list = ApplyWorkOver::getList($where, 1, $page, 20, $field, 'create_time DESC');
        if($list === false) return Json::fail('暂无数据~');

        foreach($list as &$info){
            $userInfo = User::getFind(['id' => $info['user_id']], 'name');

            $info['start_time'] = date('Y-m-d H:i', $info['start_time']);
            $info['end_time']   = date('Y-m-d H:i', $info['end_time']);
            $info['user_name']    = $userInfo['name'];

            unset($info['user_id']);
        }

        return Json::success('ok', $list, ApplyWorkOver::getNum($where));
    }

    /**
     * 加班申请通过
     */
    public function setWorkOverPass()
    {
        $workOverId = Request()->param('work_over_id', 0);
        if($workOverId === 0) return Json::fail('参数错误');

        $info = ApplyWorkOver::getFind(['id' => $workOverId]);
        if($info === false) return Json::fail('暂无数据~');
        if($info['status'] != 1) return Json::fail('状态异常');

        $flag = ApplyWorkOver::edit(['id' => $info['id'], 'status' => 2, 'approve_id' => $this->user_id, 'finish_time' => time()]);

        if($flag === false) return Json::fail('通过失败');

        SystemMenu::delRed($info['user_id'], 24, 2, 2, $workOverId);
        return Json::success('通过成功');
    }

    /**
     * 加班申请驳回
     */
    public function setWorkOverBack()
    {
        $workOverId = Request()->param('work_over_id', 0);
        $backMsg    = Request()->param('back_msg', 0);
        if($workOverId === 0 || empty($backMsg)) return Json::fail('参数错误');

        $info = ApplyWorkOver::getFind(['id' => $workOverId]);
        if($info === false) return Json::fail('暂无数据~');
        if($info['status'] != 1) return Json::fail('状态异常');

        $flag = ApplyWorkOver::edit(['id' => $info['id'], 'status' => 3, 'back_msg' => $backMsg, 'finish_time' => time()]);

        if($flag === false) return Json::fail('驳回失败');

        SystemMenu::delRed($info['user_id'], 24, 2, 2, $workOverId);
        return Json::success('驳回成功');
    }




    /**
     * 请假申请列表
     */
    public function getLeaveList()
    {
        $page   = Request()->param('page', 1);
        $search = Request()->param('search', '');

        $where = '';

        if($this->user_id != 1){
            $sectionId = User::getVal(['id' => $this->user_id], 'section_id');
            $userIds   = User::getCol([['section_id', '=', $sectionId], ['status', '=', 1]], 'id');

            $where = '(approve_id=' . $this->user_id . ' and status in (1,2)) or (send_id=' . $this->user_id . ' and status=2)';
            $where .= ' or user_id in (' . implode(',', $userIds) . ')';
        }

        $field = 'id, user_id, leave_type_id, img, reason, start_time, end_time, status, create_time';
        $list = ApplyLeave::getList($where, 1, $page, 20, $field, 'create_time DESC');
        if($list === false) return Json::fail('暂无数据~');

        foreach($list as &$info){
            $userInfo = User::getFind(['id' => $info['user_id']], 'name');

            $info['leave_type']   = ApplyLeaveType::getVal(['id' => $info['leave_type_id']], 'name');
            $info['start_time']   = date('Y-m-d H:i', $info['start_time']);
            $info['end_time']     = date('Y-m-d H:i', $info['end_time']);
            $info['user_name']    = $userInfo['name'];

            unset($info['user_id']);
            unset($info['leave_type_id']);
        }

        return Json::success('ok', $list, ApplyLeave::getNum($where));
    }

    /**
     * 请假申请通过
     */
    public function setLeavePass()
    {
        $leaveId = Request()->param('leave_id', 0);
        if($leaveId == 0) return Json::fail('参数错误' . $leaveId);

        $info = ApplyLeave::getFind(['id' => $leaveId]);
        if($info === false) return Json::fail('暂无数据~');
        if($info['status'] != 1) return Json::fail('状态异常');

        $flag = ApplyLeave::edit(['id' => $info['id'], 'status' => 2, 'approve_id' => $this->user_id, 'finish_time' => time()]);

        if($flag === false) return Json::fail('通过失败');

        SystemMenu::delRed($info['user_id'], 21, 2, 2, $leaveId);
        return Json::success('通过成功');
    }

    /**
     * 请假申请驳回
     */
    public function setLeaveBack()
    {
        $leaveId = Request()->param('leave_id', 0);
        $backMsg    = Request()->param('back_msg', 0);
        if($leaveId === 0 || empty($backMsg)) return Json::fail('参数错误');

        $info = ApplyLeave::getFind(['id' => $leaveId]);
        if($info === false) return Json::fail('暂无数据~');
        if($info['status'] != 1) return Json::fail('状态异常');

        $flag = ApplyLeave::edit(['id' => $info['id'], 'status' => 3, 'back_msg' => $backMsg, 'finish_time' => time()]);

        if($flag === false) return Json::fail('驳回失败');

        SystemMenu::delRed($info['user_id'], 21, 2, 2, $leaveId);
        return Json::success('驳回成功');
    }


    /**
     * 考勤报表导出
     */
    public function outCheckRecord()
    {
        $sectionId = Request()->param('section_id', 0);
        $time      = Request()->param('time', date('Y-m'));

        list($year, $month) = explode('-', $time);

        // $userWhere[] = ['status', '=', 1];
        $userWhere[] = ['is_staff', '=', 1];

        $checkWhere = [];

        if($sectionId != 0){
            $userWhere[]  = ['section_id', '=', $sectionId];

            $userIds = User::getCol(['section_id' => $sectionId], 'id');
            $checkWhere[] = ['user_id', 'in', $userIds];
        }

        $field = 'u.id, section_id, staff_no, u.name, check_clock_id, start_time, end_time';
        $userList = User::alias('u')
            ->where($userWhere)
            ->join('check_rule cr', 'cr.id=u.check_clock_id')
            ->field($field)
            ->order('section_id')
            ->select();

        /** 本月日期 */
        $dateList = Calendar::where(['g_year' => $year, 'g_month' => $month])->field('g_days, status')->select();

        /** 实例化 */
        $objExcel = new \PHPExcel();

        /** 设置文档属性 */
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');

        /** 创建第一张表 */
        $objExcel->setActiveSheetIndex(0);

        /** 设置内容 */
        $objActSheet = $objExcel->getActiveSheet();

        /** 表头 */
        $fieldList = ChoiceService::checkOutHeader($month);

        $number = count($fieldList);
        $letter = getLetter($number);

        /** 填充表头信息 */
        for($i = 0; $i < $number; $i++) $objActSheet->setCellValue("$letter[$i]2", "$fieldList[$i]");

        $objActSheet->mergeCells('A1:' . $letter[count($fieldList) - 1] . '1');  // 合并单元格（如果要拆分单元格是需要先合并再拆分的，否则程序会报错）
        $objExcel->setActiveSheetIndex(0)->setCellValue('A1','月度汇总表 统计日期：' . $time);
        $objActSheet->getRowDimension('1')->setRowHeight(50);
        $objActSheet->getStyle('A1')->getFont()->setBold(true);  /** 加粗 */
        $objActSheet->getStyle('A1')->getFont()->setSize(22);  /** 字体大小 */
        $objActSheet->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);  /** 字体颜色 */
        $objActSheet->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);  /** 填充颜色 */
        $objActSheet->getStyle('A1')->getFill()->getStartColor()->setARGB('FF7F24');  /** 设置填充颜色 */

        /** 全部格式 */
        $objActSheet->getStyle('A2:' . $letter[count($fieldList) - 1] . (count($userList) + 2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  /** 水平居中 */
        $objActSheet->getStyle('A1:' . $letter[count($fieldList) - 1] . (count($userList) + 2))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  /** 垂直居中 */
        $objActSheet->getDefaultColumnDimension()->setWidth(15);  /** 所有单元格（列）默认宽度 */

        /** 标题栏格式 */
        $objActSheet->getStyle('A2:' . $letter[count($fieldList) - 1] . '2')->getFont()->setBold(true);  /** 加粗 */
        $objActSheet->getStyle('A2:' . $letter[count($fieldList) - 1] . '2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);  /** 填充颜色 */
        $objActSheet->getStyle('A2:' . $letter[count($fieldList) - 1] . '2')->getFill()->getStartColor()->setARGB('FFFFCC');  /** 设置填充颜色 */
        $objActSheet->getRowDimension('2')->setRowHeight(30);  /** 设置行高度 */

        $objActSheet->freezePane('B3');  /** 冻结 */
        $objActSheet->setTitle('月度汇总');  /** sheet重命名 */

        /** 设置边框 */
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,  /** 细边框 */
                ),
            ),
        );
        $objActSheet->getStyle('A2:' . $letter[count($fieldList) - 1] . (count($userList) + 2))->applyFromArray($styleArray);

        foreach($userList as $k => $userInfo){  
            $k += 3;

            $checkNum       = 0;  /** 出勤天数 */
            $restNum        = 0;  /** 休息天数 */
            $workNeglectNum = 0;  /** 旷工天数 */
            $missCardNum    = 0;  /** 缺卡次数 */
            $lateNum        = 0;  /** 迟到次数 */
            $lateDuration   = 0;  /** 迟到时长 */
            $earlyeNum      = 0;  /** 早退次数 */
            $earlyDuration  = 0;  /** 早退时长 */

            foreach($dateList as $dateInfo){
                $checkStatus = CheckClock::getUserCheckStatus(
                    $userInfo['id'], 
                    $time . '-' . $dateInfo['g_days'],
                    $dateInfo['status'],
                    $userInfo['start_time'],
                    $userInfo['end_time'],
                );
                $objActSheet->setCellValue($letter[$dateInfo['g_days'] + 11] . $k, $checkStatus);

                if(strstr($checkStatus, '旷工') !== false 
                || strstr($checkStatus, '缺卡') !== false
                || strstr($checkStatus, '请假') !== false
                || strstr($checkStatus, '迟到') !== false
                || strstr($checkStatus, '早退') !== false
                || strstr($checkStatus, '外勤') !== false
                ){
                    $objActSheet->getStyle($letter[$dateInfo['g_days'] + 11] . $k)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);  /** 填充颜色 */
                    $objActSheet->getStyle($letter[$dateInfo['g_days'] + 11] . $k)->getFill()->getStartColor()->setARGB(ChoiceService::checkStatusColor($checkStatus));  /** 设置填充颜色 */
                }

                if($dateInfo['status'] == 2 || $dateInfo['status'] == 3){
                    $restNum++;
                    continue;
                }

                $nowsDay = $time . '-' . $dateInfo['g_days'];

                if(strtotime($nowsDay) >= strtotime(date('Y-m-d', time()))) continue;

                $clockFst = CheckClock::where(['user_id' => $userInfo['id'], 'clock_type' => 1])->whereDay('create_time', $nowsDay)->field('clock_status, status_time')->find();
                $clockSec = CheckClock::where(['user_id' => $userInfo['id'], 'clock_type' => 2])->whereDay('create_time', $nowsDay)->field('clock_status, status_time')->find();
                
                if($clockFst && $clockSec){
                    $checkNum++;
                }else if(!$clockFst && !$clockSec){
                    if(!ApplyLeave::where(['user_id' => $userInfo['id']])->whereDay('start_time|end_time', $nowsDay)->value('id')) $workNeglectNum++;
                }else{
                    $missCardNum++;
                    $checkNum++;
                }

                if($clockFst && $clockFst['clock_status'] == 2){
                    $lateNum++;
                    $lateDuration += $clockFst['status_time'];
                }

                if($clockSec && $clockSec['clock_status'] == 3){
                    $earlyeNum++;
                    $earlyDuration += $clockSec['status_time'];
                }
            }

            /** 表格内容 */
            $objActSheet->setCellValue('A' . $k, $userInfo['name']);  /** 员工名称 */
            $objActSheet->setCellValue('B' . $k, UserSection::getVal(['id' => $userInfo['section_id']], 'name'));  /** 部门 */
            $objActSheet->setCellValue('C' . $k, $userInfo['staff_no']);  /** 工号 */
            $objActSheet->setCellValue('D' . $k, $checkNum);  /** 出勤天数 */
            $objActSheet->setCellValue('E' . $k, $restNum);  /** 休息天数 */
            $objActSheet->setCellValue('F' . $k, $lateNum);  /** 迟到次数 */
            $objActSheet->setCellValue('G' . $k, secondToHour2($lateDuration));  /** 迟到时长 */
            $objActSheet->setCellValue('H' . $k, $earlyeNum);  /** 早退次数 */
            $objActSheet->setCellValue('I' . $k, secondToHour2($earlyDuration));  /** 早退时长 */
            $objActSheet->setCellValue('J' . $k, $missCardNum);  /** 缺卡次数 */
            $objActSheet->setCellValue('K' . $k, $workNeglectNum);  /** 旷工次数 */
            $objActSheet->setCellValue('L' . $k, CheckClock::getLeaveDuration($userInfo['id'], $time));  /** 请假时长 */

            $objActSheet->getRowDimension($k)->setRowHeight(30);  /** 表格高度 */
        }

        /** 创建第二张表（原始记录） */
        $objExcel->createSheet();
        $objExcel->setActiveSheetIndex(1);
        $objActSheet = $objExcel->getActiveSheet();

        /** 原始记录数据 */
        $list = CheckClock::alias('cc')
            ->join('user u', 'u.id=cc.user_id')
            ->where($checkWhere)
            ->whereMonth('cc.create_time', $time)
            ->field('cc.*, u.name as user_name')
            ->order('cc.user_id, cc.create_time')
            ->select();

        $clockType   = [1 => '上班', 2 => '下班'];
        $clockstatus = [1 => '正常', 2 => '迟到', 3 => '早退'];
        $fieldList2   = ['姓名', '打卡类型', '打卡状态', '工时', '打卡时间', '迟到/早退时长', '是否外勤'];

        $number2 = count($fieldList2);
        $letter2 = getLetter($number2);

        /** 填充表头信息 */
        for($i = 0; $i < $number2; $i++) $objActSheet->setCellValue("$letter2[$i]1", "$fieldList2[$i]");

        /** 全部格式 */
        $objActSheet->getDefaultColumnDimension()->setWidth(20);  /** 所有单元格（列）默认宽度 */
        $objActSheet->getStyle('A1:' . $letter2[count($fieldList2) - 1] . (count($list) + 1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  /** 水平居中 */
        $objActSheet->getStyle('A1:' . $letter2[count($fieldList2) - 1] . (count($list) + 1))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  /** 垂直居中 */

        /** 标题栏格式 */
        $objActSheet->getStyle('A1:' . $letter2[count($fieldList2) - 1] . '1')->getFont()->setBold(true);  /** 加粗 */
        $objActSheet->getStyle('A1:' . $letter2[count($fieldList2) - 1] . '1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);  /** 填充颜色 */
        $objActSheet->getStyle('A1:' . $letter2[count($fieldList2) - 1] . '1')->getFill()->getStartColor()->setARGB('FFFFCC');  /** 设置填充颜色 */
        $objActSheet->getRowDimension('1')->setRowHeight(30);  /** 设置行高度 */

        $objActSheet->freezePane('B2');  /** 冻结 */
        $objActSheet->setTitle('原始数据');  /** sheet重命名 */

        /** 设置边框 */
        $styleArray2 = array(
            'borders' => array(
                'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,  /** 细边框 */
                ),
            ),
        );
        $objActSheet->getStyle('A1:' . $letter2[count($fieldList2) - 1] . (count($list) + 1))->applyFromArray($styleArray2);

        /** 填充表格信息 */
        foreach($list as $kk => $info){
            $kk += 2;
            
            /** 表格内容 */
            $objActSheet->setCellValue('A' . $kk, $info['user_name']);  /** 姓名 */
            $objActSheet->setCellValue('B' . $kk, $clockType[$info['clock_type']]);  /** 打卡类型 */
            $objActSheet->setCellValue('C' . $kk, $clockstatus[$info['clock_status']]);  /** 打卡状态 */
            $objActSheet->setCellValue('D' . $kk, secondToHours($info['work_time']));  /** 工时 */
            $objActSheet->setCellValue('E' . $kk, $info['create_time']);  /** 打卡时间 */
            $objActSheet->setCellValue('F' . $kk, secondToHours($info['status_time']));  /** 迟到/早退时长 */
            $objActSheet->setCellValue('G' . $kk, $info['is_out'] ? '是' : '否');  /** 是否外勤 */

            if($clockstatus[$info['clock_status']] == '迟到'){
                $objActSheet->getStyle('C' . $kk)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);  /** 填充颜色 */
                $objActSheet->getStyle('C' . $kk)->getFill()->getStartColor()->setARGB('CCFFCC');  /** 设置填充颜色 */
            }else if($clockstatus[$info['clock_status']] == '早退'){
                $objActSheet->getStyle('C' . $kk)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);  /** 填充颜色 */
                $objActSheet->getStyle('C' . $kk)->getFill()->getStartColor()->setARGB('FFCC66');  /** 设置填充颜色 */
            }else if($info['is_out'] == 1){
                $objActSheet->getStyle('G' . $kk)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);  /** 填充颜色 */
                $objActSheet->getStyle('G' . $kk)->getFill()->getStartColor()->setARGB('99CCFF');  /** 设置填充颜色 */
            }

            $objActSheet->getRowDimension($kk)->setRowHeight(30);  /** 表格高度 */
        }
 
        $outfile = "考勤报表-" . $time . ".xlsx";

        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:attachment;filename="' . $outfile . '"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");

        $objWriter->save('php://output', true);
    }

    /**
     * 考勤报表导出测试2
     */
    public function outCheckRecordaa()
    {
        $sectionId = Request()->param('section_id', 0);
        $time      = Request()->param('time', date('Y-m'));

        list($year, $month) = explode('-', $time);

        $userWhere[] = ['status', '=', 1];
        $userWhere[] = ['is_staff', '=', 1];

        $checkWhere = [];

        if($sectionId != 0){
            $userWhere[]  = ['section_id', '=', $sectionId];

            $userIds = User::getCol(['section_id' => $sectionId], 'id');
            $checkWhere[] = ['user_id', 'in', $userIds];
        }

        $field = 'u.id, section_id, staff_no, u.name, check_clock_id, start_time, end_time';
        $userList = User::alias('u')
            ->where($userWhere)
            ->join('check_rule cr', 'cr.id=u.check_clock_id')
            ->field($field)
            ->order('section_id')
            ->select();

        /** 本月日期 */
        $dateList = Calendar::where(['g_year' => $year, 'g_month' => $month])->field('g_days, status')->select();

        $userCheckData = [];
        foreach($userList as $userInfo){
            $checkNum       = 0;  /** 出勤天数 */
            $restNum        = 0;  /** 休息天数 */
            $workNeglectNum = 0;  /** 旷工天数 */
            $missCardNum    = 0;  /** 缺卡次数 */
            $lateNum        = 0;  /** 迟到次数 */
            $lateDuration   = 0;  /** 迟到时长 */
            $earlyeNum      = 0;  /** 早退次数 */
            $earlyDuration  = 0;  /** 早退时长 */

            foreach($dateList as $dateInfo){
                $checkStatusInfo['day' . $dateInfo['g_days']] = CheckClock::getUserCheckStatus(
                    $userInfo['id'], 
                    $time . '-' . $dateInfo['g_days'],
                    $dateInfo['status'],
                    $userInfo['start_time'],
                    $userInfo['end_time'],
                );

                if($dateInfo['status'] == 2 || $dateInfo['status'] == 3){
                    $restNum++;
                    continue;
                }

                $nowsDay = $time . '-' . $dateInfo['g_days'];

                if(strtotime($nowsDay) >= strtotime(date('Y-m-d', time()))) continue;

                $clockFst = CheckClock::where(['user_id' => $userInfo['id'], 'clock_type' => 1])->whereDay('create_time', $nowsDay)->field('clock_status, status_time')->find();
                $clockSec = CheckClock::where(['user_id' => $userInfo['id'], 'clock_type' => 2])->whereDay('create_time', $nowsDay)->field('clock_status, status_time')->find();
                   
                if($clockFst && $clockSec){
                    $checkNum++;
                }else if(!$clockFst && !$clockSec){
                    $workNeglectNum++;
                }else{
                    $missCardNum++;
                    $checkNum++;
                }

                if($clockFst && $clockFst['clock_status'] == 2){
                    $lateNum++;
                    $lateDuration += $clockFst['status_time'];
                }

                if($clockSec && $clockSec['clock_status'] == 3){
                    $earlyeNum++;
                    $earlyDuration += $clockSec['status_time'];
                }
            }

            $userCheckInfo = [
                'user_name'        => $userInfo['name'],  /** 员工名称 */
                'section_name'     => UserSection::getVal(['id' => $userInfo['section_id']], 'name'),  /** 部门 */
                'staff_no'         => $userInfo['staff_no'],  /** 工号 */
                'check_num'        => $checkNum,  /** 出勤天数 */
                'rest_num'         => $restNum,  /** 休息天数 */
                'late_num'         => $lateNum,  /** 迟到次数 */
                'late_duration'    => secondToHour2($lateDuration),  /** 迟到时长 */
                'early_num'        => $earlyeNum,  /** 早退次数 */
                'early_duration'   => secondToHour2($earlyDuration),  /** 早退时长 */
                'miss_card_num'    => $missCardNum,  /** 缺卡次数 */
                'work_neglect_num' => $workNeglectNum,  /** 旷工天数 */
                'leave_num'        => 0,  /** 请假天数 */
            ];

            $userCheckData[] = array_merge($userCheckInfo, $checkStatusInfo);
        }

        /** 原始记录数据 */
        $list = CheckClock::alias('cc')
            ->join('user u', 'u.id=cc.user_id')
            ->where($checkWhere)
            ->whereMonth('cc.create_time', $time)
            ->field('cc.*, u.name as user_name')
            ->order('cc.user_id, cc.create_time')
            ->select();

        $clockType   = [1 => '上班', 2 => '下班'];
        $clockstatus = [1 => '正常', 2 => '迟到', 3 => '早退'];

        /** 填充表格信息 */
        foreach($list as $info){
            $clockInfo = [
                'user_name'    => $info['user_name'],
                'clock_type'   => $clockType[$info['clock_type']],
                'clock_status' => $clockstatus[$info['clock_status']],
                'work_time'    => secondToHours($info['work_time']),
                'create_time'  => $info['create_time'],
                'status_time'  => secondToHours($info['status_time']),
                'is_out'       => $info['is_out'] ? '是' : '否',
            ];
            
            $clockData[] = $clockInfo;
        }

        return Json::success('ok', ['check_list' => $userCheckData, 'clock_list' => $clockData]);
    }
}